from string import Template
from tack.lib import string_app, json_app, content_headers, status_codes


class Sql:
    select_legs_by_commitment_id = """
    SELECT tcl.commitment_id,
           tcl.commitment_leg_id
    FROM tmpo_commitment_leg tcl
    WHERE tcl.commitment_id = $commitment_id
    """


    delete_legs_by_commitment_id = """
    DELETE
    FROM tmpo_commitment_leg
    WHERE commitment_id = $commitment_id
    """


    delete_drivers_by_commitment_leg_id = """
    DELETE
    FROM tmpo_commitment_leg_driver
    WHERE commitment_leg_id = $commitment_leg_id
    """


    delete_vehicles_by_commitment_leg_id = """
    DELETE
    FROM tmpo_commitment_leg_vehicle
    WHERE commitment_leg_id = $commitment_leg_id
    """


def delete_legs(environ, start_response):
    commitment_id = environ['dispatcher.groups']['commitment_id']
    sql = Sql.select_legs_by_commitment_id
    sql = Template(sql).substitute(locals())
    cursor = environ['connection'].cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    for row in rows:
        commitment_leg_id = row.commitment_leg_id
        sql = Sql.delete_drivers_by_commitment_leg_id
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
        sql = Sql.delete_vehicles_by_commitment_leg_id
        sql = Template(sql).substitute(locals())
        cursor.execute(sql)
        environ['connection'].commit()
    sql = Sql.delete_legs_by_commitment_id
    sql = Template(sql).substitute(locals())
    cursor.execute(sql)
    environ['connection'].commit()
    output = 'Deleted'
    return json_app(output)(environ, start_response)
